<!DOCTYPE html>
<!-- saved from url=(0056)https://mybatis.org/mybatis-3/zh/statement-builders.html -->
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="zh" lang="zh"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="author" content="Clinton Begin">
    <meta name="author" content="Nan Lei">
    <meta name="Date-Revision-yyyymmdd" content="20200309">
    <meta http-equiv="Content-Language" content="zh">
    <title>mybatis – MyBatis 3 | SQL 语句构建器</title>
    <link rel="stylesheet" href="./mybatis – MyBatis 3 _ SQL 语句构建器_files/apache-maven-fluido-1.7.min.css">
    <link rel="stylesheet" href="./mybatis – MyBatis 3 _ SQL 语句构建器_files/site.css">
    <link rel="stylesheet" href="./mybatis – MyBatis 3 _ SQL 语句构建器_files/print.css" media="print">
    <script type="text/javascript" src="./mybatis – MyBatis 3 _ SQL 语句构建器_files/apache-maven-fluido-1.7.min.js.下载"></script>
  </head>
  <body class="topBarDisabled">
    <div class="container-fluid">
      <div id="banner">
        <div class="pull-left"><div id="bannerLeft"><h2>mybatis</h2>
</div>
</div>
        <div class="pull-right"><a href="https://mybatis.org/" id="bannerRight" title="MyBatis logo"><img src="./mybatis – MyBatis 3 _ SQL 语句构建器_files/mybatis-logo.png" alt="MyBatis logo"></a></div>
        <div class="clear"><hr></div>
      </div>

      <div id="breadcrumbs">
        <ul class="breadcrumb">
        <li id="publishDate">最近更新: 09 三月 2020<span class="divider">|</span>
</li>
          <li id="projectVersion">版本: 3.5.4</li>
        </ul>
      </div>
      <div class="row-fluid">
        <div id="leftColumn" class="span2">
          <div class="well sidebar-nav">
    <ul class="nav nav-list">
      <li class="nav-header">参考文档</li>
    <li><a href="https://mybatis.org/mybatis-3/zh/index.html" title="简介"><span class="none"></span>简介</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/getting-started.html" title="入门"><span class="none"></span>入门</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/configuration.html" title="XML 配置"><span class="icon-chevron-right"></span>XML 配置</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/sqlmap-xml.html" title="XML 映射文件"><span class="icon-chevron-right"></span>XML 映射文件</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/dynamic-sql.html" title="动态 SQL"><span class="none"></span>动态 SQL</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/java-api.html" title="Java API"><span class="icon-chevron-right"></span>Java API</a></li>
    <li class="active"><a href="https://mybatis.org/mybatis-3/zh/statement-builders.html#"><span class="none"></span>SQL 语句构建器</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/logging.html" title="日志"><span class="none"></span>日志</a></li>
      <li class="nav-header">项目文档</li>
    <li><a href="https://mybatis.org/mybatis-3/zh/project-info.html" title="项目信息"><span class="icon-chevron-right"></span>项目信息</a></li>
    <li><a href="https://mybatis.org/mybatis-3/zh/project-reports.html" title="项目报表"><span class="icon-chevron-right"></span>项目报表</a></li>
</ul>
          <hr>
          <div id="poweredBy">
            <div class="clear"></div>
            <div class="clear"></div>
            <div class="clear"></div>
            <div class="clear"></div>
<a href="http://maven.apache.org/" title="构建依靠 Maven" class="poweredBy"><img class="builtBy" alt="构建依靠 Maven" src="./mybatis – MyBatis 3 _ SQL 语句构建器_files/maven-feather.png"></a>
            </div>
          </div>
        </div>
        <div id="bodyColumn" class="span10">


  
    <div class="section">
<h2><a name="SQL_.E8.AF.AD.E5.8F.A5.E6.9E.84.E5.BB.BA.E5.99.A8"></a>SQL 语句构建器</h2>
      
<div class="section">
<h3><a name="a.E9.97.AE.E9.A2.98"></a>问题</h3>
        
<p>Java 程序员面对的最痛苦的事情之一就是在 Java 代码中嵌入 SQL 语句。这通常是因为需要动态生成 SQL 语句，不然我们可以将它们放到外部文件或者存储过程中。如你所见，MyBatis 在 XML 映射中具备强大的 SQL 动态生成能力。但有时，我们还是需要在 Java 代码里构建 SQL 语句。此时，MyBatis 有另外一个特性可以帮到你，让你从处理典型问题中解放出来，比如加号、引号、换行、格式化问题、嵌入条件的逗号管理及 AND 连接。确实，在 Java 代码中动态生成 SQL 代码真的就是一场噩梦。例如：
        </p>

        
<div class="source"><pre class="prettyprint"><span class="typ">String</span><span class="pln"> sql </span><span class="pun">=</span><span class="pln"> </span><span class="str">"SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, "</span><span class="pln">
</span><span class="str">"P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"FROM PERSON P, ACCOUNT A "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"INNER JOIN COMPANY C on D.COMPANY_ID = C.ID "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"OR (P.LAST_NAME like ?) "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"GROUP BY P.ID "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"HAVING (P.LAST_NAME like ?) "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"OR (P.FIRST_NAME like ?) "</span><span class="pln"> </span><span class="pun">+</span><span class="pln">
</span><span class="str">"ORDER BY P.ID, P.FULL_NAME"</span><span class="pun">;</span></pre></div>
      </div>
      
<div class="section">
<h3><a name="a.E8.A7.A3.E5.86.B3.E6.96.B9.E6.A1.88"></a>解决方案</h3>
      
<p>MyBatis 3 提供了方便的工具类来帮助解决此问题。借助 SQL 类，我们只需要简单地创建一个实例，并调用它的方法即可生成 SQL 语句。让我们来用 SQL 类重写上面的例子：
      </p>

      
<div class="source"><pre class="prettyprint"><span class="kwd">private</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectPersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{{</span><span class="pln">
    SELECT</span><span class="pun">(</span><span class="str">"P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME"</span><span class="pun">);</span><span class="pln">
    SELECT</span><span class="pun">(</span><span class="str">"P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON"</span><span class="pun">);</span><span class="pln">
    FROM</span><span class="pun">(</span><span class="str">"PERSON P"</span><span class="pun">);</span><span class="pln">
    FROM</span><span class="pun">(</span><span class="str">"ACCOUNT A"</span><span class="pun">);</span><span class="pln">
    INNER_JOIN</span><span class="pun">(</span><span class="str">"DEPARTMENT D on D.ID = P.DEPARTMENT_ID"</span><span class="pun">);</span><span class="pln">
    INNER_JOIN</span><span class="pun">(</span><span class="str">"COMPANY C on D.COMPANY_ID = C.ID"</span><span class="pun">);</span><span class="pln">
    WHERE</span><span class="pun">(</span><span class="str">"P.ID = A.ID"</span><span class="pun">);</span><span class="pln">
    WHERE</span><span class="pun">(</span><span class="str">"P.FIRST_NAME like ?"</span><span class="pun">);</span><span class="pln">
    OR</span><span class="pun">();</span><span class="pln">
    WHERE</span><span class="pun">(</span><span class="str">"P.LAST_NAME like ?"</span><span class="pun">);</span><span class="pln">
    GROUP_BY</span><span class="pun">(</span><span class="str">"P.ID"</span><span class="pun">);</span><span class="pln">
    HAVING</span><span class="pun">(</span><span class="str">"P.LAST_NAME like ?"</span><span class="pun">);</span><span class="pln">
    OR</span><span class="pun">();</span><span class="pln">
    HAVING</span><span class="pun">(</span><span class="str">"P.FIRST_NAME like ?"</span><span class="pun">);</span><span class="pln">
    ORDER_BY</span><span class="pun">(</span><span class="str">"P.ID"</span><span class="pun">);</span><span class="pln">
    ORDER_BY</span><span class="pun">(</span><span class="str">"P.FULL_NAME"</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}}.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span></pre></div>

      
<p>这个例子有什么特别之处吗？仔细看一下你会发现，你不用担心可能会重复出现的 "AND" 关键字，或者要做出用 "WHERE" 拼接还是 "AND" 拼接还是不用拼接的选择。SQL 类已经为你处理了哪里应该插入 "WHERE"、哪里应该使用 "AND" 的问题，并帮你完成所有的字符串拼接工作。
        </p>

      </div>

      
<div class="section">
<h3><a name="SQL_.E7.B1.BB"></a>SQL 类</h3>

        
<p>这里有一些示例：</p>

        
<div class="source"><pre class="prettyprint"><span class="com">// 匿名内部类风格</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> deletePersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{{</span><span class="pln">
    DELETE_FROM</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">);</span><span class="pln">
    WHERE</span><span class="pun">(</span><span class="str">"ID = #{id}"</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}}.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="com">// Builder / Fluent 风格</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> insertPersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="typ">String</span><span class="pln"> sql </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INSERT_INTO</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">VALUES</span><span class="pun">(</span><span class="str">"ID, FIRST_NAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{id}, #{firstName}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">VALUES</span><span class="pun">(</span><span class="str">"LAST_NAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{lastName}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> sql</span><span class="pun">;</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="com">// 动态条件（注意参数需要使用 final 修饰，以便匿名内部类对它们进行访问）</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectPersonLike</span><span class="pun">(</span><span class="kwd">final</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> id</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">final</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> firstName</span><span class="pun">,</span><span class="pln"> </span><span class="kwd">final</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> lastName</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{{</span><span class="pln">
    SELECT</span><span class="pun">(</span><span class="str">"P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME"</span><span class="pun">);</span><span class="pln">
    FROM</span><span class="pun">(</span><span class="str">"PERSON P"</span><span class="pun">);</span><span class="pln">
    </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">id </span><span class="pun">!=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
      WHERE</span><span class="pun">(</span><span class="str">"P.ID like #{id}"</span><span class="pun">);</span><span class="pln">
    </span><span class="pun">}</span><span class="pln">
    </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">firstName </span><span class="pun">!=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
      WHERE</span><span class="pun">(</span><span class="str">"P.FIRST_NAME like #{firstName}"</span><span class="pun">);</span><span class="pln">
    </span><span class="pun">}</span><span class="pln">
    </span><span class="kwd">if</span><span class="pln"> </span><span class="pun">(</span><span class="pln">lastName </span><span class="pun">!=</span><span class="pln"> </span><span class="kwd">null</span><span class="pun">)</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
      WHERE</span><span class="pun">(</span><span class="str">"P.LAST_NAME like #{lastName}"</span><span class="pun">);</span><span class="pln">
    </span><span class="pun">}</span><span class="pln">
    ORDER_BY</span><span class="pun">(</span><span class="str">"P.LAST_NAME"</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}}.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> deletePersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{{</span><span class="pln">
    DELETE_FROM</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">);</span><span class="pln">
    WHERE</span><span class="pun">(</span><span class="str">"ID = #{id}"</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}}.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> insertPersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{{</span><span class="pln">
    INSERT_INTO</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">);</span><span class="pln">
    VALUES</span><span class="pun">(</span><span class="str">"ID, FIRST_NAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{id}, #{firstName}"</span><span class="pun">);</span><span class="pln">
    VALUES</span><span class="pun">(</span><span class="str">"LAST_NAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{lastName}"</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}}.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> updatePersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{{</span><span class="pln">
    UPDATE</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">);</span><span class="pln">
    SET</span><span class="pun">(</span><span class="str">"FIRST_NAME = #{firstName}"</span><span class="pun">);</span><span class="pln">
    WHERE</span><span class="pun">(</span><span class="str">"ID = #{id}"</span><span class="pun">);</span><span class="pln">
  </span><span class="pun">}}.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span></pre></div>

        
<table border="0" class="table table-striped">
          <thead>
            
<tr class="a">
              
<th>方法</th>
              
<th>描述</th>
            </tr>
          </thead>
          <tbody>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>SELECT(String)</tt>
                  </li>
                  
<li>
                    <tt>SELECT(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>开始新的或追加到已有的 <tt>SELECT</tt>子句。可以被多次调用，参数会被追加到 <tt>SELECT</tt> 子句。
                 参数通常使用逗号分隔的列名和别名列表，但也可以是数据库驱动程序接受的任意参数。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                
<ul>
                  
<li>
                    <tt>SELECT_DISTINCT(String)</tt>
                  </li>
                  
<li>
                    <tt>SELECT_DISTINCT(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>开始新的或追加到已有的 <tt>SELECT</tt>子句，并添加 <tt>DISTINCT</tt> 关键字到生成的查询中。可以被多次调用，参数会被追加到 <tt>SELECT</tt> 子句。
                 参数通常使用逗号分隔的列名和别名列表，但也可以是数据库驱动程序接受的任意参数。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>FROM(String)</tt>
                  </li>
                  
<li>
                    <tt>FROM(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>开始新的或追加到已有的 <tt>FROM</tt>子句。可以被多次调用，参数会被追加到
                <tt>FROM</tt>子句。
                参数通常是一个表名或别名，也可以是数据库驱动程序接受的任意参数。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                
<ul>
                  
<li>
                    <tt>JOIN(String)</tt>
                  </li>
                  
<li>
                    <tt>JOIN(String...)</tt>
                  </li>
                  
<li>
                    <tt>INNER_JOIN(String)</tt>
                  </li>
                  
<li>
                    <tt>INNER_JOIN(String...)</tt>
                  </li>
                  
<li>
                    <tt>LEFT_OUTER_JOIN(String)</tt>
                  </li>
                  
<li>
                    <tt>LEFT_OUTER_JOIN(String...)</tt>
                  </li>
                  
<li>
                    <tt>RIGHT_OUTER_JOIN(String)</tt>
                  </li>
                  
<li>
                    <tt>RIGHT_OUTER_JOIN(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>基于调用的方法，添加新的合适类型的 <tt>JOIN</tt> 子句。
				参数可以包含一个由列和连接条件构成的标准连接。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>WHERE(String)</tt>
                  </li>
                  
<li>
                    <tt>WHERE(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>插入新的 <tt>WHERE</tt> 子句条件，并使用 <tt>AND</tt> 拼接。可以被多次调用，对于每一次调用产生的新条件，会使用 <tt>AND</tt> 拼接起来。要使用 <tt>OR</tt> 分隔，请使用 <tt>OR()</tt>。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                <tt>OR()</tt>
              </td>
              
<td>使用 <tt>OR</tt> 来分隔当前的 <tt>WHERE</tt> 子句条件。
				可以被多次调用，但在一行中多次调用会生成错误的 <tt>SQL</tt>。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                <tt>AND()</tt>
              </td>
              
<td>使用 <tt>AND</tt> 来分隔当前的 <tt>WHERE</tt>子句条件。
				可以被多次调用，但在一行中多次调用会生成错误的 <tt>SQL</tt>。由于 <tt>WHERE</tt> 和 <tt>HAVING</tt>都会自动使用 <tt>AND</tt> 拼接, 因此这个方法并不常用，只是为了完整性才被定义出来。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                
<ul>
                  
<li>
                    <tt>GROUP_BY(String)</tt>
                  </li>
                  
<li>
                    <tt>GROUP_BY(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>追加新的 <tt>GROUP BY</tt> 子句，使用逗号拼接。可以被多次调用，每次调用都会使用逗号将新的条件拼接起来。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>HAVING(String)</tt>
                  </li>
                  
<li>
                    <tt>HAVING(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>追加新的 <tt>HAVING</tt> 子句。使用 AND 拼接。可以被多次调用，每次调用都使用<tt>AND</tt>来拼接新的条件。要使用 <tt>OR</tt> 分隔，请使用 <tt>OR()</tt>。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                
<ul>
                  
<li>
                    <tt>ORDER_BY(String)</tt>
                  </li>
                  
<li>
                    <tt>ORDER_BY(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>追加新的 <tt>ORDER BY</tt> 子句，使用逗号拼接。可以多次被调用，每次调用会使用逗号拼接新的条件。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>LIMIT(String)</tt>
                  </li>
                  
<li>
                    <tt>LIMIT(int)</tt>
                  </li>
                </ul>
              </td>
              
<td>
                追加新的 <tt>LIMIT</tt> 子句。
				仅在 SELECT()、UPDATE()、DELETE() 时有效。
				当在 SELECT() 中使用时，应该配合 OFFSET() 使用。（于 3.5.2 引入）
              </td>
            </tr>
            
<tr class="a">
              
<td>
                
<ul>
                  
<li>
                    <tt>OFFSET(String)</tt>
                  </li>
                  
<li>
                    <tt>OFFSET(long)</tt>
                  </li>
                </ul>
              </td>
              
<td>
                追加新的 <tt>OFFSET</tt> 子句。
                仅在 SELECT() 时有效。
                当在 SELECT() 时使用时，应该配合 LIMIT() 使用。（于 3.5.2 引入）
              </td>
            </tr>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>OFFSET_ROWS(String)</tt>
                  </li>
                  
<li>
                    <tt>OFFSET_ROWS(long)</tt>
                  </li>
                </ul>
              </td>
              
<td>
			    追加新的 <tt>OFFSET n ROWS</tt> 子句。
				仅在 SELECT() 时有效。
				该方法应该配合 FETCH_FIRST_ROWS_ONLY() 使用。（于 3.5.2 加入）
              </td>
            </tr>
            
<tr class="a">
              
<td>
                
<ul>
                  
<li>
                    <tt>FETCH_FIRST_ROWS_ONLY(String)</tt>
                  </li>
                  
<li>
                    <tt>FETCH_FIRST_ROWS_ONLY(int)</tt>
                  </li>
                </ul>
              </td>
              
<td>
			  	追加新的 <tt>FETCH FIRST n ROWS ONLY</tt> 子句。
				仅在 SELECT() 时有效。
				该方法应该配合 OFFSET_ROWS() 使用。（于 3.5.2 加入）
              </td>
            </tr>
            
<tr class="b">
              
<td>
                <tt>DELETE_FROM(String)</tt>
              </td>
              
<td>开始新的 delete 语句，并指定删除表的表名。通常它后面都会跟着一个 WHERE 子句！
              </td>
            </tr>
            
<tr class="a">
              
<td>
                <tt>INSERT_INTO(String)</tt>
              </td>
              
<td>开始新的 insert 语句，并指定插入数据表的表名。后面应该会跟着一个或多个 VALUES() 调用，或 INTO_COLUMNS() 和 INTO_VALUES() 调用。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                
<ul>
                  
<li>
                    <tt>SET(String)</tt>
                  </li>
                  
<li>
                    <tt>SET(String...)</tt>
                  </li>
                </ul>
              </td>
              
<td>对 update 语句追加 "set" 属性的列表</td>
            </tr>
            
<tr class="a">
              
<td>
                <tt>UPDATE(String)</tt>
              </td>
              
<td>开始新的 update 语句，并指定更新表的表名。后面都会跟着一个或多个 SET() 调用，通常也会有一个 WHERE() 调用。 
              </td>
            </tr>
            
<tr class="b">
              
<td>
                <tt>VALUES(String, String)</tt>
              </td>
              
<td>追加数据值到 insert 语句中。第一个参数是数据插入的列名，第二个参数则是数据值。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                <tt>INTO_COLUMNS(String...)</tt>
              </td>
              
<td>
                追加插入列子句到 insert 语句中。应与 INTO_VALUES() 一同使用。
              </td>
            </tr>
            
<tr class="b">
              
<td>
                <tt>INTO_VALUES(String...)</tt>
              </td>
              
<td>
                追加插入值子句到 insert 语句中。应与 INTO_COLUMNS() 一同使用。
              </td>
            </tr>
            
<tr class="a">
              
<td>
                <tt>ADD_ROW()</tt>
              </td>
              
<td>
                添加新的一行数据，以便执行批量插入。（于 3.5.2 引入）
              </td>
            </tr>
          </tbody>
        </table>

        
<p>
          <span class="label important">提示</span>
          注意，SQL 类将原样插入 <tt>LIMIT</tt>、<tt>OFFSET</tt>、<tt>OFFSET n ROWS</tt> 以及 <tt>FETCH FIRST n ROWS ONLY</tt> 子句。换句话说，类库不会为不支持这些子句的数据库执行任何转换。
		  因此，用户应该要了解目标数据库是否支持这些子句。如果目标数据库不支持这些子句，产生的 SQL 可能会引起运行错误。
        </p>

        
<p>从版本 3.4.2 开始，你可以像下面这样使用可变长度参数：</p>

<div class="source"><pre class="prettyprint"><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectPersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">SELECT</span><span class="pun">(</span><span class="str">"P.ID"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"A.USERNAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"A.PASSWORD"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"P.FULL_NAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"D.DEPARTMENT_NAME"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"C.COMPANY_NAME"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">FROM</span><span class="pun">(</span><span class="str">"PERSON P"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"ACCOUNT A"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INNER_JOIN</span><span class="pun">(</span><span class="str">"DEPARTMENT D on D.ID = P.DEPARTMENT_ID"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"COMPANY C on D.COMPANY_ID = C.ID"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">WHERE</span><span class="pun">(</span><span class="str">"P.ID = A.ID"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"P.FULL_NAME like #{name}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">ORDER_BY</span><span class="pun">(</span><span class="str">"P.ID"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"P.FULL_NAME"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> insertPersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INSERT_INTO</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INTO_COLUMNS</span><span class="pun">(</span><span class="str">"ID"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"FULL_NAME"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INTO_VALUES</span><span class="pun">(</span><span class="str">"#{id}"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{fullName}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> updatePersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">UPDATE</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">SET</span><span class="pun">(</span><span class="str">"FULL_NAME = #{fullName}"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"DATE_OF_BIRTH = #{dateOfBirth}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">WHERE</span><span class="pun">(</span><span class="str">"ID = #{id}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span></pre></div>

        
<p>从版本 3.5.2 开始，你可以像下面这样构建批量插入语句：</p>

        
<div class="source"><pre class="prettyprint"><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> insertPersonsSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="com">// INSERT INTO PERSON (ID, FULL_NAME)</span><span class="pln">
  </span><span class="com">//     VALUES (#{mainPerson.id}, #{mainPerson.fullName}) , (#{subPerson.id}, #{subPerson.fullName})</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INSERT_INTO</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INTO_COLUMNS</span><span class="pun">(</span><span class="str">"ID"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"FULL_NAME"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INTO_VALUES</span><span class="pun">(</span><span class="str">"#{mainPerson.id}"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{mainPerson.fullName}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">ADD_ROW</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">INTO_VALUES</span><span class="pun">(</span><span class="str">"#{subPerson.id}"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"#{subPerson.fullName}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span></pre></div>

        
<p>从版本 3.5.2 开始，你可以像下面这样构建限制返回结果数的 SELECT 语句,：</p>

        
<div class="source"><pre class="prettyprint"><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectPersonsWithOffsetLimitSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="com">// SELECT id, name FROM PERSON</span><span class="pln">
  </span><span class="com">//     LIMIT #{limit} OFFSET #{offset}</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">SELECT</span><span class="pun">(</span><span class="str">"id"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"name"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">FROM</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">LIMIT</span><span class="pun">(</span><span class="str">"#{limit}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">OFFSET</span><span class="pun">(</span><span class="str">"#{offset}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">

</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectPersonsWithFetchFirstSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="com">// SELECT id, name FROM PERSON</span><span class="pln">
  </span><span class="com">//     OFFSET #{offset} ROWS FETCH FIRST #{limit} ROWS ONLY</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> SQL</span><span class="pun">()</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">SELECT</span><span class="pun">(</span><span class="str">"id"</span><span class="pun">,</span><span class="pln"> </span><span class="str">"name"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">FROM</span><span class="pun">(</span><span class="str">"PERSON"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">OFFSET_ROWS</span><span class="pun">(</span><span class="str">"#{offset}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">FETCH_FIRST_ROWS_ONLY</span><span class="pun">(</span><span class="str">"#{limit}"</span><span class="pun">)</span><span class="pln">
    </span><span class="pun">.</span><span class="pln">toString</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span></pre></div>

      </div>

      
<div class="section">
<h3><a name="SqlBuilder_.E5.92.8C_SelectBuilder_.28.E5.B7.B2.E7.BB.8F.E5.BA.9F.E5.BC.83.29"></a>SqlBuilder 和 SelectBuilder (已经废弃)</h3>
        
<p>
          在版本 3.2 之前，我们的实现方式不太一样，我们利用 ThreadLocal 变量来掩盖一些对 Java DSL 不太友好的语言限制。现在，现代 SQL 构建框架使用的构建器和匿名内部类思想已被人们所熟知。因此，我们废弃了基于这种实现方式的 SelectBuilder 和 SqlBuilder 类。
        </p>
        
<p>
          下面的方法仅仅适用于废弃的 SqlBuilder 和 SelectBuilder 类。
        </p>
        
<table border="0" class="table table-striped">
        <thead>
          
<tr class="a">
            
<th>方法</th>
            
<th>描述</th>
          </tr>
        </thead>
        <tbody>
        
<tr class="b">
          
<td>
            <tt>BEGIN()</tt>
            /
            <tt>RESET()</tt>
          </td>
          
<td>这些方法清空 SelectBuilder 类的 ThreadLocal 状态，并准备好构建一个新的语句。开始新的语句时，<tt>BEGIN()</tt> 是最名副其实的（可读性最好的）。但如果由于一些原因（比如程序逻辑在某些条件下需要一个完全不同的语句），在执行过程中要重置语句构建状态，就很适合使用 <tt>RESET()</tt>。
          </td>
        </tr>
        
<tr class="a">
          
<td>
            <tt>SQL()</tt>
          </td>
          
<td>该方法返回生成的 <tt>SQL()</tt> 并重置 <tt>SelectBuilder</tt> 状态（等价于调用了 <tt>BEGIN()</tt> 或 <tt>RESET()</tt>）。因此，该方法只能被调用一次！
          </td>
        </tr>
        </tbody>
        </table>

        
<p>
          SelectBuilder 和 SqlBuilder 类并不神奇，但最好还是知道它们的工作原理。
          SelectBuilder 以及 SqlBuilder 借助静态导入和 ThreadLocal 变量实现了对插入条件友好的简洁语法。要使用它们，只需要静态导入这个类的方法即可，就像这样（只能使用其中的一条，不能同时使用）:
        </p>

        
<div class="source"><pre class="prettyprint"><span class="kwd">import</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> org</span><span class="pun">.</span><span class="pln">apache</span><span class="pun">.</span><span class="pln">ibatis</span><span class="pun">.</span><span class="pln">jdbc</span><span class="pun">.</span><span class="typ">SelectBuilder</span><span class="pun">.*;</span></pre></div>
        
<div class="source"><pre class="prettyprint"><span class="kwd">import</span><span class="pln"> </span><span class="kwd">static</span><span class="pln"> org</span><span class="pun">.</span><span class="pln">apache</span><span class="pun">.</span><span class="pln">ibatis</span><span class="pun">.</span><span class="pln">jdbc</span><span class="pun">.</span><span class="typ">SqlBuilder</span><span class="pun">.*;</span></pre></div>

        
<p>然后就可以像下面这样创建一些方法：</p>

        
<div class="source"><pre class="prettyprint"><span class="com">/* 已被废弃 */</span><span class="pln">
</span><span class="kwd">public</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectBlogsSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">BEGIN</span><span class="pun">();</span><span class="pln"> </span><span class="com">// 重置 ThreadLocal 状态变量</span><span class="pln">
  SELECT</span><span class="pun">(</span><span class="str">"*"</span><span class="pun">);</span><span class="pln">
  FROM</span><span class="pun">(</span><span class="str">"BLOG"</span><span class="pun">);</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> SQL</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
        </span></pre></div>

        
<div class="source"><pre class="prettyprint"><span class="com">/* 已被废弃 */</span><span class="pln">
</span><span class="kwd">private</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> selectPersonSql</span><span class="pun">()</span><span class="pln"> </span><span class="pun">{</span><span class="pln">
  </span><span class="kwd">BEGIN</span><span class="pun">();</span><span class="pln"> </span><span class="com">// 重置 ThreadLocal 状态变量</span><span class="pln">
  SELECT</span><span class="pun">(</span><span class="str">"P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME"</span><span class="pun">);</span><span class="pln">
  SELECT</span><span class="pun">(</span><span class="str">"P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON"</span><span class="pun">);</span><span class="pln">
  FROM</span><span class="pun">(</span><span class="str">"PERSON P"</span><span class="pun">);</span><span class="pln">
  FROM</span><span class="pun">(</span><span class="str">"ACCOUNT A"</span><span class="pun">);</span><span class="pln">
  INNER_JOIN</span><span class="pun">(</span><span class="str">"DEPARTMENT D on D.ID = P.DEPARTMENT_ID"</span><span class="pun">);</span><span class="pln">
  INNER_JOIN</span><span class="pun">(</span><span class="str">"COMPANY C on D.COMPANY_ID = C.ID"</span><span class="pun">);</span><span class="pln">
  WHERE</span><span class="pun">(</span><span class="str">"P.ID = A.ID"</span><span class="pun">);</span><span class="pln">
  WHERE</span><span class="pun">(</span><span class="str">"P.FIRST_NAME like ?"</span><span class="pun">);</span><span class="pln">
  OR</span><span class="pun">();</span><span class="pln">
  WHERE</span><span class="pun">(</span><span class="str">"P.LAST_NAME like ?"</span><span class="pun">);</span><span class="pln">
  GROUP_BY</span><span class="pun">(</span><span class="str">"P.ID"</span><span class="pun">);</span><span class="pln">
  HAVING</span><span class="pun">(</span><span class="str">"P.LAST_NAME like ?"</span><span class="pun">);</span><span class="pln">
  OR</span><span class="pun">();</span><span class="pln">
  HAVING</span><span class="pun">(</span><span class="str">"P.FIRST_NAME like ?"</span><span class="pun">);</span><span class="pln">
  ORDER_BY</span><span class="pun">(</span><span class="str">"P.ID"</span><span class="pun">);</span><span class="pln">
  ORDER_BY</span><span class="pun">(</span><span class="str">"P.FULL_NAME"</span><span class="pun">);</span><span class="pln">
  </span><span class="kwd">return</span><span class="pln"> SQL</span><span class="pun">();</span><span class="pln">
</span><span class="pun">}</span><span class="pln">
        </span></pre></div>

      </div>


    </div>
  


        </div>
      </div>
    </div>
    <hr>
    <footer>
      <div class="container-fluid">
        <div class="row-fluid">
            <p>Copyright ©2009–2020
<a href="http://www.mybatis.org/">MyBatis.org</a>.
All rights reserved.</p>
        </div>
      </div>
    </footer>
  

<div class="xl-chrome-ext-bar" id="xl_chrome_ext_{4DB361DE-01F7-4376-B494-639E489D19ED}" style="display: none;">
      <div class="xl-chrome-ext-bar__logo"></div>

      <a id="xl_chrome_ext_download" href="javascript:;" class="xl-chrome-ext-bar__option">下载视频</a>
      <a id="xl_chrome_ext_close" href="javascript:;" class="xl-chrome-ext-bar__close"></a>
    </div></body></html>